import pymysql;
import datetime;
from RadomChar import id_generator;

'''
定义获取数据库链接的方法
'''
def getConnection():
    try:
        connection = pymysql.connect(host='localhost',
                                 user='travelgo',
                                 password='travelgo365',
                                 db='travelgo',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor);
        return connection;
    except Exception:
        print('exception occours when get connection to the mysql!');
        return None;

'''
保存青旅信息
'''
def saveOrUpdateHotelInfo(hotel_info):
    hotel_id = hotel_info['id'];
    conn = getConnection();
    try:
        with conn.cursor() as cursor:
            HOTEL_FACILITY = 'HF001,HF003,HF004,HF005,HF006,HF007,HF008,HF009,HF010,HF011,HF012,HF013,HF014,HF015,HF016,HF017,HF018,HF019,HF020,HF021,HF022,HF023,HF024,HF025,HF026';
            if(hotel_info['pics'] is None or len(hotel_info['pics']) <= 0):
                sql = "INSERT INTO HOTEL_INFO(HOTEL_ID,HOTEL_NAME,IS_PARTNER,HOTEL_DESC,ROOM_NUM,RECENT_DECORATE_TIME,PROVINCE,CITY,PLACE_NAME,PLACE_LONGITUDE,PLACE_LATITUDE,TELEPHONE,HOTEL_FACILITY,UPDATE_DATETIME,STAT_FLAG) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)";
                cursor.execute(sql, (hotel_info['id'], hotel_info['name'],'N',hotel_info['desc'],hotel_info['rooms_num'],hotel_info['decorate_time'],hotel_info['province'],hotel_info['city'],hotel_info['address'],hotel_info['longitude'],hotel_info['latitude'],hotel_info['phone'],HOTEL_FACILITY, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'I'));

                sql = "INSERT INTO HOTEL_CITY_INFO(CITY_ID,HOTEL_ID,HOTEL_LEVEL,LOWEST_PRICE,STAT_FLAG) values(%s, %s, %s, %s, %s)";
                cursor.execute(sql, (hotel_info['city'], hotel_info['id'], 1, 0.0, 'I'));
            else:
                sql = "INSERT INTO HOTEL_INFO(HOTEL_ID,HOTEL_NAME,IS_PARTNER,HOTEL_DESC,ROOM_NUM,RECENT_DECORATE_TIME,PROVINCE,CITY,PLACE_NAME,PLACE_LONGITUDE,PLACE_LATITUDE,TELEPHONE,COVER_PHOTO,SMALL_PHOTO,HOTEL_FACILITY,UPDATE_DATETIME,STAT_FLAG) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)";
                cursor.execute(sql, (hotel_info['id'], hotel_info['name'],'N',hotel_info['desc'],hotel_info['rooms_num'],hotel_info['decorate_time'],hotel_info['province'],hotel_info['city'],hotel_info['address'],hotel_info['longitude'],hotel_info['latitude'],hotel_info['phone'],hotel_info['pics'][0]['photo_url'],hotel_info['pics'][0]['small_photo_url'],HOTEL_FACILITY, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'I'));

                sql = "INSERT INTO HOTEL_CITY_INFO(CITY_ID,HOTEL_ID,HOTEL_LEVEL,LOWEST_PRICE,STAT_FLAG) values(%s, %s, %s, %s, %s)";
                cursor.execute(sql, (hotel_info['city'], hotel_info['id'], 1, 0.0, 'I'));

        conn.commit();
        #print('hotel_base_info save or update success');

        ROOM_FACILITY = 'RF001,RF002,RF003,RF004,RF005,RF006,RF007,RF008,RF009,RF010,RF011,RF012,RF013,RF014,RF015,RF016,RF017,RF018,RF019';
        for rooms in hotel_info['rooms_desc']:
            with conn.cursor() as cursor:
                sql = "INSERT INTO HOTEL_ROOM_INFO(HOTEL_ID,ROOM_NAME,ROOM_DESC,ROOM_PRICE,ROOM_FACILITY,UPDATE_DATETIME) VALUES(%s,%s,%s,%s,%s,%s)";
                cursor.execute(sql, (int(hotel_id), rooms['room_name'],rooms['room_name'],rooms['room_price'],ROOM_FACILITY,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")));
            conn.commit();
        #print('hotel_rooms_info save success');

        min_room_id = 0;
        with conn.cursor() as cursor:
            sql = "SELECT MIN(ID) FROM HOTEL_ROOM_INFO WHERE HOTEL_ID = %s";
            cursor.execute(sql, hotel_id);
            min_room_id = cursor.fetchone()['MIN(ID)'];
        #print(min_room_id);

        for pics in hotel_info['pics']:
            with conn.cursor() as cursor:
                sql = "INSERT INTO HOTEL_PHOTO_INFO(PHOTO_ID,PHOTO_URL,SMALL_URL,CATEGORY_ID,SUB_CATEGORY_ID,HOTEL_ID,STAT_FLAG,UPDATE_DATETIME) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)";
                cursor.execute(sql, (id_generator(),pics['photo_url'],pics['small_photo_url'],4,0,int(hotel_id),'N',datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")));
                cursor.execute(sql, (id_generator(),pics['photo_url'],pics['small_photo_url'],2,min_room_id,int(hotel_id),'N',datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")));
            conn.commit();
        #print('hotel_photos_info save success');
    except pymysql.err.InternalError as e:
        print('mysql error when operate hotel_id:' + str(hotel_id) + repr(e));
    finally:
        conn.close();

'''
为了避免每次爬取青旅数据的不一致性,多次保存所爬取的青旅列表
'''
def saveHotelList(hotelList):
    conn = getConnection();
    try:
        for hotel in hotelList:
            with conn.cursor() as cursor:
                sql = "REPLACE INTO SPIDER_HOTEL_INFO(HOTEL_ID,HOTEL_NAME,CITY,UPDATE_DATETIME) values(%s, %s, %s, %s)";
                cursor.execute(sql, (hotel['id'], hotel['name'], hotel['city'], datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")));
            conn.commit();
    except pymysql.err.InternalError as e:
        print('mysql error when operate saveHotelList:' + repr(e));
    finally:
        conn.close();


'''
获取所有爬取的青旅列表
'''
def queryAllHotelList(cityName):
    conn = getConnection();
    resultSet = [];
    try:
        with conn.cursor() as cursor:
            sql = "SELECT HOTEL_ID, CITY FROM SPIDER_HOTEL_INFO where CITY like %s";
            cursor.execute(sql, (cityName));
            resultSet = cursor.fetchall();
        return resultSet;
    except pymysql.err.InternalError as e:
        print('query all spiderHotelList error:' + repr(e));
        return None;
    finally:
        conn.close();

'''
根据城市名称获取城市编号
'''
def queryCityCodeByName(cityName):
    conn = getConnection();
    provinceAndCityId = {};
    try:
        cityName = "%"+ cityName + "%";
        with conn.cursor() as cursor:
            sql = "SELECT AREA_ID,AREA_NAME,PARENT_ID FROM AREA_INFO WHERE AREA_NAME like %s and AREA_LEVEL = %s";
            cursor.execute(sql, (cityName, 3));
            result = cursor.fetchone();
            if(result is None):
                return None;
            else:
                provinceAndCityId['city'] = result['AREA_ID'];
                provinceAndCityId['province'] = result['PARENT_ID'];
                provinceAndCityId['name'] = result['AREA_NAME'];
                return provinceAndCityId;
    except pymysql.err.InternalError as e:
        print('mysql error when operate saveHotelList:' + repr(e));
        return None;
    finally:
        conn.close();

'''
查询该青旅信息已经已经爬取下来
'''
def queryHotelInfoExist(hotelId):
    conn = getConnection();
    flag = False;
    try:
        with conn.cursor() as cursor:
            sql = "SELECT count(1) FROM HOTEL_INFO WHERE HOTEL_ID = %s";
            cursor.execute(sql, (hotelId));
            if(cursor.fetchone()['count(1)'] == 1):
                flag = True;
        return flag;
    except pymysql.err.InternalError as e:
        print('mysql error when query hotel exist,hotel_id =:' + str(hotelId) + repr(e));
        return flag;
    finally:
        conn.close();

'''
查询该城市是否已经爬取
'''
def updateSpideredCity(cityId, cityName):
    conn = getConnection();
    try:
        with conn.cursor() as cursor:
            sql = "REPLACE INTO HOTEL_CITY_LEVEL(CITY_ID,CITY_NAME,CITY_LEVEL,STAT_FLAG) values(%s, %s, %s, %s)";
            cursor.execute(sql, (cityId, cityName, 1, 'I'));
        conn.commit();
    except pymysql.err.InternalError as e:
        print('mysql error when update spideCity ,cityId =:' + cityId + ',cityName=' + cityName + repr(e));
    finally:
        conn.close();
